//
//  SQLOperation.m
//  YYWeather
//
//  Created by 姚英 on 16/7/7.
//  Copyright © 2016年 yy. All rights reserved.
//

#import "SQLOperation.h"
#import "SQLiteManager.h"

@implementation SQLOperation

/// 数据库名
static NSString * const sqlName = @"City.db";
/// cityList表名
static NSString * const tabName = @"t_cityList";
/// 最近访问city表名
static NSString * const lastName = @"t_lastCity";
/// 热门城市
static NSString * const hotName = @"t_hotCity";
/// 当前城市
static NSString * const locationName = @"t_locationCity";


#pragma mark - 打开数据库

/// 判断数据库是否存在,不存在则拷贝,并打开数据库
+ (void)openDB{
    SQLiteManager *sqlManager = [SQLiteManager shareSQLiteManager];
    
    // 获取缓存路径
    NSString *cachePath = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES)[0];
    
    // 拼接文件路径
    NSString *DBPath = [cachePath stringByAppendingPathComponent:sqlName];
    
    YYLog(@"%@",DBPath)

    NSFileManager *fileM = [NSFileManager defaultManager];
    
    if ([fileM fileExistsAtPath:DBPath]) {
        YYLog(@"数据库已存在")
    }else{
        // 将数据库复制到缓存
        NSString *path = [[NSBundle mainBundle] pathForResource:sqlName ofType:nil];
        
        NSData *dataDB = [NSData dataWithContentsOfFile:path];
        if ([dataDB writeToFile:DBPath atomically:YES] == YES) {
            YYLog(@"数据库拷贝成功")
        }else{
            YYLog(@"数据库拷贝失败")
        }
    }

    // 打开数据库
    if ([sqlManager openDB:DBPath] == NO) {
        YYLog(@"数据库打开失败")
    }else{
        YYLog(@"数据库打开成功")
    }
}

#pragma mark - 查询

/// 读取数据库记录的本地城市
+(NSArray *)readLocationCity
{
    SQLiteManager *sqlManager = [SQLiteManager shareSQLiteManager];
    
    // 查询数据库记录的本地城市
    NSString *querSQL =
    [NSString stringWithFormat:@"SELECT city FROM %@ WHERE ID = 1 ;",locationName];
    
    
    NSLog(@"_________   %@", querSQL);
    
    
    NSArray *result = [sqlManager queryData:querSQL];
    if (result.count == 0) {
        YYLog(@"readLocationCity失败")
    }
    YYLog(@"readLocationCity result = %@",result)
    
    // 去掉表头，将字典数组转换为城市数组
    NSMutableArray *strResult = [NSMutableArray array];
    for (NSDictionary *dict in result) {
        [strResult addObject:dict[@"city"]];
        
        NSLog(@" strResult = %@",strResult);
    }
    
    return strResult;
}

/// 读取最近访问的城市
+(NSArray *)readLastCity
{
    SQLiteManager *sqlManager = [SQLiteManager shareSQLiteManager];
    
    //最近访问
    NSString *querSQL =
        [NSString stringWithFormat:@"SELECT city FROM %@ order by ID DESC limit 6 ;",lastName];
//    NSString *querSQL = [NSString stringWithFormat:@"SELECT city FROM %@ WHERE id < 7 ;",lastName];

    NSArray *result = [sqlManager queryData:querSQL];
    if (result.count == 0) {
        YYLog(@"readLastCity失败")
    }
    YYLog(@"readLastCity result = %@",result)
    
    // 去掉表头，将字典数组转换为城市数组
    NSMutableArray *strResult = [NSMutableArray array];
    for (NSDictionary *dict in result) {
        [strResult addObject:dict[@"city"]];
    }
    
    return strResult;
}

/// 读取热门城市
+(NSArray *)readHotCity
{
    SQLiteManager *sqlManager = [SQLiteManager shareSQLiteManager];
    
    //热门城市
    NSString *querSQL =
    [NSString stringWithFormat:@"SELECT city FROM %@ WHERE id < 7 ;",hotName];
    
    NSArray *result = [sqlManager queryData:querSQL];
    if (result.count == 0) {
        YYLog(@"读取数据失败")
    }
    YYLog(@"result = %@",result)
    
    // 去掉表头，将字典数组转换为城市数组
    NSMutableArray *strResult = [NSMutableArray array];
    for (NSDictionary *dict in result) {
        [strResult addObject:dict[@"city"]];
    }
    
    return strResult;
}

/// 读取城市列表
+(NSArray *)readCityList:(NSString *)key
{
    
    SQLiteManager *sqlManager = [SQLiteManager shareSQLiteManager];
    
    // 按城市拼音首字母搜索
    NSString *querSQL =
    [NSString stringWithFormat:@"SELECT city FROM %@ WHERE key like \"%@%%\" ;",tabName,key];
    // 拼音查询
    NSArray *result = [sqlManager queryData:querSQL];
    if (result.count == 0) {
        // 如果搜索不到内容，则按汉字搜索
        querSQL = [NSString stringWithFormat:@"SELECT city FROM %@ WHERE city like \"%@%%\" ;",tabName,key];
        result = [sqlManager queryData:querSQL];
        
        // 去掉“市”
        if (result.count == 0 && key.length > 2) {
            NSString *city = [SQLOperation readCity:[key substringToIndex:2]];
            querSQL = [NSString stringWithFormat:@"SELECT city FROM %@ WHERE city like \"%@%%\" ;",tabName,city];
            result = [sqlManager queryData:querSQL];
          //   YYLog(@"读取数据失败")
            if (result.count == 0) {
                YYLog(@"读取数据失败")
            }
        }
    }
    YYLog(@"result = %@",result)
    return result;
}

/// 查询该城市是否在数据库中,并且唯一。
+(NSString *)readCity:(NSString *)city
{
    
    SQLiteManager *sqlManager = [SQLiteManager shareSQLiteManager];
    
    NSString *querSQL =
    [NSString stringWithFormat:@"SELECT city FROM %@ WHERE city like \"%@%%\" ;",tabName,city];
     NSArray *result = [sqlManager queryData:querSQL];
    
    YYLog(@"result = %@",result)
    if (result.count == 1) {
        return result[0][@"city"];
    }
    return nil;
    
}

///// 读取最近访问
//+(NSArray *)readLastCity{
//    
//    SQLiteManager *sqlManager = [SQLiteManager shareSQLiteManager];
//    
//    // 查询
//    NSString *querSQL = [NSString stringWithFormat:@"SELECT city FROM %@;",lastName];
//
//    NSArray *result = [sqlManager queryData:querSQL];
//    if (result.count == 0) {
//        // 没有数据
//        YYLog(@"没有最近访问的数据")
//    }
//    
//    NSLog(@"result = %@", result);
//    
//    return result;
//}

#pragma mark - 写数据库


//UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];
//"insert into bulktest2 (y) values ('YYY');"

/// 写入当前城市
+(BOOL)writeLocationCity:(NSString *)str
{
    SQLiteManager *sqlManager = [SQLiteManager shareSQLiteManager];
    
    // updata location
    NSString *querSQL =
    [NSString stringWithFormat:@"UPDATE %@ SET city = \"%@\" ;",locationName,str];
    
    return [sqlManager executeData:querSQL];
}


/// 写入最近访问的城市
// 逻辑：先读取ID最大的6个城市，判断是否重复，如果重复，则将该城市的ID修改为最大值+1，如果没有重复，则插入该城市(ID为自增的)
+(BOOL)writeLastCity:(NSString *)str
{
    SQLiteManager *sqlManager = [SQLiteManager shareSQLiteManager];
    
    //读取ID最大的6个城市
    NSArray *lastCity = [self readLastCity];
    YYLog(@"lastCity - %@", lastCity)
    //检查是否重复
    for (NSString *obj in lastCity) {
        YYLog(@"str = %@ ,obj = %@ ",str ,obj)
        if ([str isEqualToString:obj]) {
            // 修改ID为最大值+1
            // 逻辑 ： 先查询出目前的最大ID ，再查询出这个当前城市的最大ID（如果用城市来修改ID，会出现错误，因为这个城市可能检索出多个ID值来，ID值是唯一的，不能把这些ID都改为同一个值，就会造成更新失败），在将当前城市最大ID值去更新为目前ID最大值+1.
            int countMAX = 0;
            int countCur = 0;
            
            // 查询出最大ID
            NSString *querSQL =
            [NSString stringWithFormat:@"SELECT ID FROM %@ order by ID DESC limit 1 ;",lastName];
            NSArray *result = [sqlManager queryData:querSQL];
        
            YYLog(@"read ID = %@",result)
        
            if (result.count != 0) {
                NSString *countStr = result[0][@"id"];
//                YYLog(@"%@",countStr)
                countMAX = (int)[countStr intValue]+ 1;
                YYLog(@"countMAX = %i", countMAX);
            }else{
                YYLog(@"查询最大ID失败！")
            }
            
            // 查询出当前城市最大ID
            querSQL =
            [NSString stringWithFormat:@"SELECT ID FROM %@ WHERE city = \"%@\" order by ID DESC limit 1 ;",lastName,str];
            result = [sqlManager queryData:querSQL];
            
            YYLog(@"read ID = %@",result)
            
            if (result.count != 0) {
                NSString *countStr = result[0][@"id"];
//                YYLog(@"%@",countStr)
                countCur = (int)[countStr intValue];
                YYLog(@"countCur = %i", countCur);
            }else{
                YYLog(@"查询当前ID失败！")
            }

            // UPDATE t_lastCity SET id= 61 WHERE city = "青岛";
            YYLog(@"更新的城市 = %@",str)
            querSQL =
            [NSString stringWithFormat:@"UPDATE %@ SET id = %i WHERE id = %i ;",lastName,countMAX,countCur];
//            NSLog(@"UPDATE ID =  %i",[sqlManager executeData:querSQL]);
            return [sqlManager executeData:querSQL];
        }
    }
    //没有重复，插入数据
    NSString *querSQL =
                [NSString stringWithFormat:@"insert into %@ (city) values (\"%@\");",lastName,str];
    return [sqlManager executeData:querSQL];

}


/// 写入热门城市
//+(BOOL)writeHotCity:(NSArray *)arr
//{
//    SQLiteManager *sqlManager = [SQLiteManager shareSQLiteManager];
//    
//    //热门城市
//    NSString *querSQL =
//    [NSString stringWithFormat:@"SELECT city FROM %@ WHERE id < 7 ;",hotName];
//    
//    NSArray *result = [sqlManager queryData:querSQL];
//    if (result.count == 0) {
//        YYLog(@"读取数据失败")
//    }
//    YYLog(@"result = %@",result)
//    return result;
//}

/// 插入城市列表
//+(int)readCityList:(NSString *)key{
//    
//    SQLiteManager *sqlManager = [SQLiteManager shareSQLiteManager];
//    
//    NSString *querSQL =
//    [NSString stringWithFormat:@"SELECT city FROM %@ WHERE key like \"%@%%\" ;",tabName,key];
//    // 拼音查询
//    NSArray *result = [sqlManager queryData:querSQL];
//    if (result.count == 0) {
//        YYLog(@"读取数据失败")
//    }
//    YYLog(@"result = %@",result)
//    return result;
//}


@end
